﻿using HPIT_Library;
using HPIT_Library_Modal;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace HPIT_Library_DAL
{
   public class bookSerivers
    {
        /// <summary>
        /// 更改书籍
        /// </summary>
        /// <param name="books"></param>
        /// <returns></returns>
        public static int Update(Books books)
        {
            string sql = "update dbo.books set  BookName=@BookName,BookSynopsis=@BookSynopsis,BookAuthor=@BookAuthor,BookMoney=@BookMoney where BookId=@BookId";
            SqlParameter[] pams = { 
            new SqlParameter("@BookName",books.BookName),

            new SqlParameter("@BookSynopsis",books.BookSynopsis),

            new SqlParameter("@BookAuthor",books.BookAuthor),

            new SqlParameter("@BookMoney",books.BookMoney),
            new SqlParameter("@BookId",books.BookId)
            };
            return DbHelper.ExecuteNonQuery(sql, pams);

        
        }
        public static Books SelectID(int id)
        {
            string sql = "select * from [Books] where BookId=@BookId";
            SqlParameter sqlParameter = new SqlParameter("@BookId", id);

            SqlDataReader reader = DbHelper.ExecuteReader(sql, sqlParameter);
            Books user = null;
            if (reader.Read())
            {
                user = new Books()
                {
                    BookId = Convert.ToInt32(reader["BookId"]),
                    BookName = Convert.ToString(reader["BookName"]),
                    BookAuthor = Convert.ToString(reader["BookAuthor"]),
                    BookMoney = Convert.ToString(reader["BookMoney"]),
                    BookSynopsis = Convert.ToString(reader["BookSynopsis"]),

                };
            }
            reader.Close();
            return user;
        }
        /// <summary>
        /// 查询书
        /// </summary>
        /// <param name="BookName"></param>
        /// <param name="BookAuthor"></param>
        /// <param name="CategoryId"></param>
        /// <returns></returns>
        public static DataTable Select(string BookName = "",string BookAuthor="",int CategoryId = 0)
        {
            string sql = "select * from Books,Category where Books.BookClassId=category.CategoryId";
            if (BookName!="")
            {
                sql += " and BookName like @BookName";
            }
            if (BookAuthor!="")
            {
                sql += " and BookAuthor like @BookAuthor";
            }
            if (CategoryId != 0)
            {
                sql += " and CategoryId=@CategoryId";
            }
            SqlParameter[] pams = { 
                new SqlParameter("@BookName","%"+BookName+"%"),
                new SqlParameter("@BookAuthor","%"+BookAuthor+"%"),
                new SqlParameter("@CategoryId",CategoryId)
            };
            return DbHelper.ExecuteTable(sql, pams);
        }
        public static List<Books> GetBooksPageList(BooksSerichModel serichModel, int PageIndex, int PageSize,ref int TotalCount)
        {
            string proc = "Proc_Books";
            SqlParameter[] pams =
                {
                new SqlParameter("@BookName",serichModel.BookName),
                new SqlParameter("@BookAuthor",serichModel.BookAuthor),

                new SqlParameter("@PageIndex",PageIndex),
                new SqlParameter("@PageSize",PageSize),
                new SqlParameter("@TotalCount",TotalCount),
            };
            pams[4].Direction = ParameterDirection.Output;
            SqlDataReader reader= DbHelper.ExecuteReaderProc(proc, pams);
            List<Books> list = new List<Books>();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Books books = new Books();
                    books.BookId = Convert.ToInt32(reader["BookId"]);
                    books.BookName = Convert.ToString(reader["BookName"]);
                    books.BookSynopsis= Convert.ToString(reader["BookSynopsis"]);
                    books.BookMoney= Convert.ToString(reader["BookMoney"]);
                    books.BookAuthor= Convert.ToString(reader["BookAuthor"]);
                    list.Add(books);
                }
                reader.Close();
                TotalCount = Convert.ToInt32(pams[4].Value);

            }
            return list;
        }
    }
}
